CREATE VIEW [dbo].[vOpportunitySalesReport]
AS
SELECT DISTINCT
                      OM.Id, OM.OpportunityStatusCode, OM.CurrentActionPlanStageName, OM.WinProbability, OM.ExpirationDate, OM.OpportunityKey, OMS.DecisionDate,
                      OMS.PotentialSales, OMS.ActualSales, OMS.Product, OMS.TimingProbability, OMS.Units, OMS.Quality, OMS.ResponseMedia, OT.OpportunityClass,
                      OT.Name AS OpportunityType, '' AS OwnerSubRoleNames, '' AS ContactSubRoleNames, CMOG.FullName AS OwnerName,
                      CMP.FullName AS ProspectName, CMC.FullName AS ContactName, GRR.GroupRoleDesc, OM.Description
FROM         dbo.OpportunityMain AS OM INNER JOIN
                      dbo.OpportunityMainSales AS OMS ON OM.OpportunityKey = OMS.OpportunityKey LEFT OUTER JOIN
                      dbo.OpportunityType AS OT ON OM.OpportunityTypeKey = OT.OpportunityTypeKey LEFT OUTER JOIN
                      dbo.GroupMember AS GMEMOG ON OM.OpportunityOwnerGroupKey = GMEMOG.GroupKey LEFT OUTER JOIN
                      dbo.ContactMain AS CMOG ON GMEMOG.MemberContactKey = CMOG.ContactKey LEFT OUTER JOIN
                      dbo.ContactMain AS CMP ON OM.ProspectKey = CMP.ContactKey LEFT OUTER JOIN
                      dbo.GroupMember AS GMEMCG ON OM.OpportunityContactGroupKey = GMEMCG.GroupKey LEFT OUTER JOIN
                      dbo.ContactMain AS CMC ON GMEMCG.MemberContactKey = CMC.ContactKey LEFT OUTER JOIN
                      dbo.GroupMemberDetail AS GMD ON GMEMOG.GroupMemberKey = GMD.GroupMemberKey LEFT OUTER JOIN
                      dbo.GroupRoleRef AS GRR ON GMD.GroupRoleKey = GRR.GroupRoleKey

GO